//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.ProfTitle;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class ProfTitleDao {
    private static Collection<ProfTitle> profTitles;
    private static ProfTitleDao profTitleDao = null;

    private ProfTitleDao() {
    }

    public static ProfTitleDao getInstance() {
        if (profTitleDao == null){
            profTitleDao = new ProfTitleDao();
        }
        return profTitleDao;
    }

    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"profTitle");
    }

    public Collection<ProfTitle> findAll(
            Connection connection, List<Condition> conditions_json, Pagination pagination)
            throws SQLException{

        //创建ProfTitle类型集合类对象，HashSet不能保证结果中元素的顺序
        Collection<ProfTitle> desriedProTitles = new HashSet<ProfTitle>();

        int totalNum = profTitleDao.getInstance().count(connection);

        //创建查询profTitle的主语句
        StringBuilder profTitle_sql = new StringBuilder("select * from profTitle");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            profTitle_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            profTitle_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(pagination.toString());
        //获得Department对象的结果集
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建Department对象
            ProfTitle profTitle = new ProfTitle(
                    resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")
            );
            desriedProTitles.add(profTitle);
        }
        //关闭对象
        JdbcHelper.close(resultSet, pstmt);
        return desriedProTitles;
    }

    public ProfTitle find(Integer id,Connection connection)throws SQLException{
        String deleteProfTitle_sql = "select * from profTitle where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt =connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找学院
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建degree对象
        ProfTitle profTitle = new ProfTitle(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks")
        );
        //关闭
        JdbcHelper.close(pstmt,connection);
        return profTitle;
    }

    public boolean update(ProfTitle profTitle,Connection connection)throws SQLException{
        String updateProfTitle_sql =
                "UPDATE profTitle SET no = ?,description = ?,remarks = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1,profTitle.getNo());
        pstmt.setString(2,profTitle.getDescription());
        pstmt.setString(3,profTitle.getRemarks());
        pstmt.setInt(4,profTitle.getId());
        return pstmt.executeUpdate()>0;
    }

    public boolean add(ProfTitle profTitle,Connection connection)throws SQLException {
        String addProfTitle_sql =
                "INSERT INTO profTitle(no,description,remarks) VALUES" +"(?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1,profTitle.getNo());
        pstmt.setString(2,profTitle.getDescription());
        pstmt.setString(3,profTitle.getRemarks());
        //添加学院 并判断是否添加成功
        return pstmt.executeUpdate()>0;
    }

//    public boolean delete(Integer id)throws SQLException{
//        ProfTitle profTitle = this.find(id);
//        return this.delete(profTitle);
//    }

    public boolean delete(ProfTitle profTitle,Connection connection)throws SQLException{
        String addProfTitle_sql = "delete from profTitle where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1,profTitle.getNo());
        //根据id删除学院 并判断是否删除成功
        return pstmt.executeUpdate()>0;
    }
}

